clear *

use "${root}/data/processed/final_sample.dta", clear

drop if irregular == 1

** MERGE CITY CHARACTERISTICS DATA -
use "${root}/data/processed/final_sample.dta", clear

merge 1:1 mun_code year using "${root}/data/processed/demographic_dataset.dta"

* more manageable name for labor force participation rate and education variables
rename econ_active_workforce lfp
gen high_educ=bachelor_perc
gen low_educ=illiteracy_perc

* gen log of population
gen pop = mun_pop/1000

* gen log of bolsa familia
gen bf_number = monthly_avg_households/1000
gen bf_value_pc = total_annual_value_pc_bf

*gen log of average earnings
label var avg_earnings "Average earnings (2016 constant values)"
label var median_earnings "Median earnings (2016 constant values)"

*gen amendments as share of total revenues
gen tot_rev_sum2y = tot_rev_cp_t_0 + tot_rev_cp_t_minus1
gen amend_exec_sum2y = amendment_executed_cp_t_0 + amendment_executed_cp_t_minus1
gen amend_auth_sum2y = amendment_authorized_cp_t_0 + amendment_authorized_cp_t_minus1

gen amend_exec_share2y = amend_exec_sum2y/tot_rev_sum2y*100
gen amend_auth_share2y = amend_auth_sum2y/tot_rev_sum2y*100



local covariates pop urb_perc white_perc median_earnings high_educ low_educ bf_number bf_value_pc amend_auth_share2y amend_exec_share2y lfp north northeast southeast south midwest

foreach outcome of local covariates{


*** Summary Baseline
	sum `outcome' if baseline_sample == 1
	local min_`outcome'_1 : di %9.2fc `r(min)'
	local max_`outcome'_1 : di %9.2fc `r(max)'
	local mean_`outcome'_1 : di %9.2fc `r(mean)'
	local sd_`outcome'_1 : di %9.2fc `r(sd)'
	local N_`outcome'_1 : di %8.0g `r(N)'

*** Summary Tiebout median
	sum `outcome' if baseline_sample == 1 & lame_duck == 1
	local min_`outcome'_2 : di %9.2fc `r(min)'
	local max_`outcome'_2 : di %9.2fc `r(max)'
	local mean_`outcome'_2 : di %9.2fc `r(mean)'
	local sd_`outcome'_2 : di %9.2fc `r(sd)'
	local N_`outcome'_2 : di %8.0g `r(N)'

*** Summary Tiebout 75th
	sum `outcome' if baseline_sample == 1 & tiebout_median_sample == 1
	local min_`outcome'_3 : di %9.2fc `r(min)'
	local max_`outcome'_3 : di %9.2fc `r(max)'
	local mean_`outcome'_3 : di %9.2fc `r(mean)'
	local sd_`outcome'_3 : di %9.2fc `r(sd)'
	local N_`outcome'_3 : di %8.0g `r(N)'

*** Summary Lame Duck
	sum `outcome' if baseline_sample == 1 & coal_dist_median_sample == 1
	local min_`outcome'_4 : di %9.2fc `r(min)'
	local max_`outcome'_4 : di %9.2fc `r(max)'
	local mean_`outcome'_4 : di %9.2fc `r(mean)'
	local sd_`outcome'_4 : di %9.2fc `r(sd)'
	local N_`outcome'_4 : di %8.0g `r(N)'

*** Summary Oil Windfall
	sum `outcome' if baseline_sample == 1 & oil_sample == 1
	local min_`outcome'_5 : di %9.2fc `r(min)'
	local max_`outcome'_5 : di %9.2fc `r(max)'
	local mean_`outcome'_5 : di %9.2fc `r(mean)'
	local sd_`outcome'_5 : di %9.2fc `r(sd)'
	local N_`outcome'_5 : di %8.0g `r(N)'

}

***** LATEX TABLE ****

* write table
texdoc init "${root}/results/tables/summary_stats_covariates.tex", replace force

tex \caption{Covariates descriptive statistics} 
tex \resizebox{\linewidth}{!}{
tex \begin{tabularx}{\linewidth}{l *5{>{\Centering}X}}
tex \toprule

tex 													&        Baseline 				& \multicolumn{4}{c}{Subsamples} 								\\
tex \cmidrule(lr){2-2} \cmidrule(lr){3-6}

tex 													& 								& 	Lame Duck 			& Tiebout $<$ median	 								& Ideology distance $>$ median  						& Oil windfall					\\
tex \midrule
tex \multicolumn{6}{l}{Labor market and demographic covariates} \\
tex \midrule
tex Median earnings  								&		`mean_median_earnings_1'		&		`mean_median_earnings_2'		&		`mean_median_earnings_3'		&		`mean_median_earnings_4'			&		`mean_median_earnings_5'			\\
tex 							  								&		(`sd_median_earnings_1')		&		(`sd_median_earnings_2')		&		(`sd_median_earnings_3')		&		(`sd_median_earnings_4')			&		(`sd_median_earnings_5')			\\
tex Labor force participation       &		`mean_lfp_1'								&		`mean_lfp_2'								&		`mean_lfp_3'								&		`mean_lfp_4'									&		`mean_lfp_5'									\\
tex 													      &		(`sd_lfp_1')								&		(`sd_lfp_2')								&		(`sd_lfp_3')								&		(`sd_lfp_4')									&		(`sd_lfp_5')									\\
tex Population (in thousands)    									&		`mean_pop_1'								&		`mean_pop_2'								&		`mean_pop_3'								&		`mean_pop_4'									&		`mean_pop_5'									\\
tex  												     	&		(`sd_pop_1')								&		(`sd_pop_2')								&		(`sd_pop_3')								&		(`sd_pop_4')									&		(`sd_pop_5')									\\
tex \% Urban   											&		`mean_urb_perc_1'						&		`mean_urb_perc_2'						&		`mean_urb_perc_3'						&		`mean_urb_perc_4'							& 	`mean_urb_perc_5'							\\
tex 				   											&		(`sd_urb_perc_1')						&		(`sd_urb_perc_2')						&		(`sd_urb_perc_3')						&		(`sd_urb_perc_4')							& 	(`sd_urb_perc_5')							\\
tex \% White             						&		`mean_white_perc_1'					&		`mean_white_perc_2'					&		`mean_white_perc_3'					&		`mean_white_perc_4'						&		`mean_white_perc_5'						\\
tex 				             						&		(`sd_white_perc_1')					&		(`sd_white_perc_2')					&		(`sd_white_perc_3')					&		(`sd_white_perc_4')						&		(`sd_white_perc_5')						\\
tex \% Higher education  						&		`mean_high_educ_1'					&		`mean_high_educ_2'					&		`mean_high_educ_3'					&		`mean_high_educ_4'						&		`mean_high_educ_5'						\\
tex 									  						&		(`sd_high_educ_1')					&		(`sd_high_educ_2')					&		(`sd_high_educ_3')					&		(`sd_high_educ_4')						&		(`sd_high_educ_5')						\\
tex \% Illiterate 									&		`mean_low_educ_1'						&		`mean_low_educ_2'						&		`mean_low_educ_3'						&		`mean_low_educ_4'							&		`mean_low_educ_5'							\\
tex 							 									&		(`sd_low_educ_1')						&		(`sd_low_educ_2')						&		(`sd_low_educ_3')						&		(`sd_low_educ_4')							&		(`sd_low_educ_5')							\\
tex \midrule
tex \multicolumn{6}{l}{Geographic covariates} \\
tex \midrule
tex North  			    								&		`mean_north_1'							&		`mean_north_2'							&		`mean_north_3'							&		`mean_north_4'								&		`mean_north_5'								\\
tex			  			    								&		(`sd_north_1')							&		(`sd_north_2')							&		(`sd_north_3')							&		(`sd_north_4')								&		(`sd_north_5')								\\
tex Northeast  			       					&		`mean_northeast_1'					&  `mean_northeast_2'					&		`mean_northeast_3'					&		`mean_northeast_4'						&		`mean_northeast_5'						\\
tex 				  			       					&		(`sd_northeast_1')					&  (`sd_northeast_2')					&		(`sd_northeast_3')					&		(`sd_northeast_4')						&		(`sd_northeast_5')						\\
tex South      											&		`mean_south_1'							&		`mean_south_2'							&		`mean_south_3'							&		`mean_south_4'								&		`mean_south_5'								\\
tex 		      											&		(`sd_south_1')							&		(`sd_south_2')							&		(`sd_south_3')							&		(`sd_south_4')								&		(`sd_south_5')								\\
tex Southeast  											&		`mean_southeast_1'					&  `mean_southeast_2'					&		`mean_southeast_3'					&		`mean_southeast_4'						&		`mean_southeast_5'						\\
tex 				  											&		(`sd_southeast_1')					&  (`sd_southeast_2')					&		(`sd_southeast_3')					&		(`sd_southeast_4')						&		(`sd_southeast_5')						\\
tex Midwest    											&		`mean_midwest_1'						& 	`mean_midwest_2'						&		`mean_midwest_3'						&		`mean_midwest_4'							&		`mean_midwest_5'							\\
tex 			    											&		(`sd_midwest_1')						& 	(`sd_midwest_2')						&		(`sd_midwest_3')						&		(`sd_midwest_4')							&		(`sd_midwest_5')							\\
tex \midrule
tex \multicolumn{6}{l}{Other covariates} \\
tex \midrule
tex Bolsa Familia (households)						      	&		`mean_bf_number_1'					&		`mean_bf_number_2'					&		`mean_bf_number_3'					&		`mean_bf_number_4'						&		`mean_bf_number_5'						\\
tex 								&		(`sd_bf_number_1')					&		(`sd_bf_number_2')					&		(`sd_bf_number_3')					&		(`sd_bf_number_4')						&		(`sd_bf_number_5')						\\
tex Bolsa Familia (receipts)      						&		`mean_bf_value_pc_1'				&		`mean_bf_value_pc_2'				&		`mean_bf_value_pc_3'				&		`mean_bf_value_pc_4'					&		`mean_bf_value_pc_5'					\\
tex								&		(`sd_bf_value_pc_1')				&		(`sd_bf_value_pc_2')				&		(`sd_bf_value_pc_3')				&		(`sd_bf_value_pc_4')					&		(`sd_bf_value_pc_5')					\\
tex Authorized amendments 						&		`mean_amend_auth_share2y_1'	&		`mean_amend_auth_share2y_2'	&		`mean_amend_auth_share2y_3'	&		`mean_amend_auth_share2y_4'		&		`mean_amend_auth_share2y_5'		\\
tex		&		(`sd_amend_auth_share2y_1')	&		(`sd_amend_auth_share2y_2')	&		(`sd_amend_auth_share2y_3')	&		(`sd_amend_auth_share2y_4')		&		(`sd_amend_auth_share2y_5')		\\
tex Executed amendments 						&		`mean_amend_exec_share2y_1'	&		`mean_amend_exec_share2y_2'	&		`mean_amend_exec_share2y_3'	&		`mean_amend_exec_share2y_4'		&		`mean_amend_exec_share2y_5'		\\
tex 	 	&		(`sd_amend_exec_share2y_1')	&		(`sd_amend_exec_share2y_2')	&		(`sd_amend_exec_share2y_3')	&		(`sd_amend_exec_share2y_4')		&		(`sd_amend_exec_share2y_5')		\\
tex \midrule
tex Number of obs. 									& `N_north_1' 				& `N_north_2' 				& `N_north_3'				 & `N_north_4'					 & `N_north_5'					 \\
tex \bottomrule
tex \end{tabularx}}

texdoc close
